﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DBUtlity;
using Models;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class PJSampleOutService
    {
        #region 新增
        public bool InsertInfo(PJSampleOut main)
        {
            string mainSql = "insert into PJSampleOuts(WorkParentId,SerialNum,OrderType,OrderStartSerialNum,";
            mainSql += "OutDate,Customer,CustomerAddress,Person,Bill,CustomerService,ExpressNo,TEL)";
            mainSql += " values(@WorkParentId,@SerialNum,@OrderType,@OrderStartSerialNum,";
            mainSql += "@OutDate,@Customer,@CustomerAddress,@Person,@Bill,@CustomerService,@ExpressNo,@TEL)";
            SqlParameter[] mainParam = new SqlParameter[]
            {
                new SqlParameter("@WorkParentId",main.WorkParentId),
                new SqlParameter("@SerialNum",main.SerialNum),
                new SqlParameter("@OrderType",main.OrderType),
                new SqlParameter("@OrderStartSerialNum",main.OrderStartSerialNum),
                new SqlParameter("@OutDate",main.OutDate),
                new SqlParameter("@Customer",main.Customer),
                new SqlParameter("@CustomerAddress",main.CustomerAddress),
                new SqlParameter("@Person",main.Person),
                new SqlParameter("@Bill",main.Bill),
                new SqlParameter("@CustomerService",main.CustomerService),
                new SqlParameter("@ExpressNo",main.ExpressNo),
                new SqlParameter("@TEL",main.Tel)
            };
            string detailSql = "insert into PJSampleChildOuts(Row,SerialNum,CustomerOrder,Maktx,";
            detailSql += "[Description],Num,Rev,Ps)";
            detailSql += " values(@Row,@SerialNum,@CustomerOrder,@Maktx,";
            detailSql += "@Description,@Num,@Rev,@Ps)";
            List<SqlParameter[]> detailParam = new List<SqlParameter[]>();
            for (int i = 0; i < main.ChildList.Count; i++)
            {
                detailParam.Add(new SqlParameter[]
                {
                    new SqlParameter("@Row",i+1),
                    new SqlParameter("@SerialNum",main.ChildList[i].SerialNum),
                    new SqlParameter("@CustomerOrder",main.ChildList[i].CustomerOrder),
                    new SqlParameter("@Maktx",main.ChildList[i].Maktx),
                    new SqlParameter("@Description",main.ChildList[i].Description),
                    new SqlParameter("@Num",main.ChildList[i].Num),
                    new SqlParameter("@Rev",main.ChildList[i].Rev),
                    new SqlParameter("@Ps",main.ChildList[i].Ps)
                });
            }
            return SQLHelper.UpdateByTran(mainSql, mainParam, detailSql, detailParam);
        }
        public bool InsertChildInfo(PJSampleOut main)
        {
            string detailSql = "insert into PJSampleChildOuts(Row,SerialNum,CustomerOrder,Maktx,";
            detailSql += "[Description],Num,Rev,Ps)";
            detailSql += " values(@Row,@SerialNum,@CustomerOrder,@Maktx,";
            detailSql += "@Description,@Num,@Rev,@Ps)";
            List<SqlParameter[]> detailParam = new List<SqlParameter[]>();
            for (int i = 0; i < main.ChildList.Count; i++)
            {
                detailParam.Add(new SqlParameter[]
                {
                    new SqlParameter("@Row",main.ChildList[i].Row),
                    new SqlParameter("@SerialNum",main.ChildList[i].SerialNum),
                    new SqlParameter("@CustomerOrder",main.ChildList[i].CustomerOrder),
                    new SqlParameter("@Maktx",main.ChildList[i].Maktx),
                    new SqlParameter("@Description",main.ChildList[i].Description),
                    new SqlParameter("@Num",main.ChildList[i].Num),
                    new SqlParameter("@Rev",main.ChildList[i].Rev),
                    new SqlParameter("@Ps",main.ChildList[i].Ps)
                });
            }
            return SQLHelper.UpdateByTran(null, null, detailSql, detailParam);
        }
        #endregion

        #region 更改
        public bool UpdateInfo(PJSampleOut main)
        {
            string mainSql = "update PJSampleOuts set";
            mainSql += " OutDate=@OutDate,Customer=@Customer,CustomerAddress=@CustomerAddress,Person=@Person,";
            mainSql += "CustomerService=@CustomerService,ExpressNo=@ExpressNo,TEL=@TEL";
            mainSql += " where WorkParentId=@WorkparentId";
            SqlParameter[] mainParam = new SqlParameter[]
            {
                new SqlParameter("@WorkParentId",main.WorkParentId),
                new SqlParameter("@OutDate",main.OutDate),
                new SqlParameter("@Customer",main.Customer),
                new SqlParameter("@CustomerAddress",main.CustomerAddress),
                new SqlParameter("@Person",main.Person),
                new SqlParameter("@CustomerService",main.CustomerService),
                new SqlParameter("@ExpressNo",main.ExpressNo),
                new SqlParameter("@TEL",main.Tel)
                //new SqlParameter("@Bill",main.Bill)
            };
            string detailSql = "update PJSampleChildOuts set SerialNum=@SerialNum,";
            detailSql += "CustomerOrder=@CustomerOrder,Maktx=@Maktx,Description=@Description,Num=@Num,";
            detailSql += "Rev=@Rev,Ps=@Ps";
            detailSql += " where SerialNum=@SerialNum and Row=@Row";
            List<SqlParameter[]> detailParam = new List<SqlParameter[]>();
            for (int i = 0; i < main.ChildList.Count; i++)
            {
                detailParam.Add(new SqlParameter[]
                {
                    new SqlParameter("@Row",i+1),
                    new SqlParameter("@SerialNum",main.ChildList[i].SerialNum),
                    new SqlParameter("@CustomerOrder",main.ChildList[i].CustomerOrder),
                    new SqlParameter("@Maktx",main.ChildList[i].Maktx),
                    new SqlParameter("@Description",main.ChildList[i].Description),
                    new SqlParameter("@Num",main.ChildList[i].Num),
                    new SqlParameter("@Rev",main.ChildList[i].Rev),
                    new SqlParameter("@Ps",main.ChildList[i].Ps)
                });
            }
            return SQLHelper.UpdateByTran(mainSql, mainParam, detailSql, detailParam);
        }
        public int UpdateSaleOrder(string saleOrder, int workParentId)
        {
            string sql = "update PJSampleOuts set SaleOrder=@SaleOrder";
            sql += " where WorkParentId=@WorkParentId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@SaleOrder",saleOrder),
                new SqlParameter("@WorkParentId",workParentId)
            };
            return SQLHelper.Update(sql, param);
        }
        public int UpdateBill(string bill, int workParentId)
        {
            string sql = "update PJSampleOuts set Bill=@Bill";
            sql += " where WorkParentId=@WorkParentId";
            SqlParameter[] param = new SqlParameter[]
            {
                 new SqlParameter("@Bill",bill),
                new SqlParameter("@WorkParentId",workParentId)
            };
            return SQLHelper.Update(sql, param);
        }
        public int UpdateBill2(string bill2, int workParentId)
        {
            string sql = "update PJSampleOuts set Bill2=@Bill2";
            sql += " where WorkParentId=@WorkParentId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@Bill2",bill2),
                new SqlParameter("@WorkParentId",workParentId)
            };
            return SQLHelper.Update(sql, param);
        }
        public int UpdateStatues(int workParentId)
        {
            string sql = "update PJSampleOuts set Statues=@Statues";
            sql += " where WorkParentId=@WorkParentId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@Statues","1"),
                new SqlParameter("@WorkParentId",workParentId)
            };
            return SQLHelper.Update(sql, param);
        }
        #endregion

        #region 删除
        public int DelChildList(string serialNum, int row)
        {
            string sql = "delete from PJSampleChildOuts where SerialNum=@SerialNum and Row>@Row";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@SerialNum",serialNum),
                new SqlParameter("@Row",row)
            };
            return SQLHelper.Update(sql, param);
        }
        #endregion

        #region 查询
        /// <summary>
        /// 获取最大Id行的流水号
        /// </summary>
        /// <returns></returns>
        public string GetLastSerialNum(string orderType,string right2)
        {
            string sql = "select SerialNum from PJSampleOuts";
            sql += " where Id=(select Max(Id) from PJSampleOuts";
            sql += " where OrderType=@OrderType and right(OrderStartSerialNum,2)=@Right2)";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@OrderType",orderType),
                new SqlParameter("@Right2",right2)
            };
            return Convert.ToString(SQLHelper.GetSingleResult(sql, param));
        }

        public PJSampleOut GetSampleOut(int workParentId)
        {
            string sql = "select * from PJSampleOuts";
            sql += " left join PJSampleChildOuts on PJSampleOuts.SerialNum=PJSampleChildOuts.SerialNum";
            sql += " where WorkParentId=@WorkParentId";
            PJSampleOut objPJSampleOut = null;
            List<PJSampleChildOut> list = new List<PJSampleChildOut>();
            SqlDataReader objReader = SQLHelper.GetReader(sql, new SqlParameter[] { new SqlParameter("@WorkParentId", workParentId) });
            while (objReader.Read())
            {
                objPJSampleOut = new PJSampleOut()
                {
                    Id = Convert.ToInt32(objReader["Id"]),
                    WorkParentId = Convert.ToInt32(objReader["WorkParentId"]),
                    SerialNum = objReader["SerialNum"].ToString(),
                    OrderType = objReader["OrderType"].ToString(),
                    OrderStartSerialNum = objReader["OrderStartSerialNum"].ToString(),
                    OutDate = objReader["OutDate"] is DBNull ? Convert.ToDateTime("1970-01-01") : Convert.ToDateTime(objReader["OutDate"]),
                    Customer=objReader["Customer"].ToString(),
                    CustomerAddress = objReader["CustomerAddress"].ToString(),
                    Person = objReader["Person"].ToString(),

                    SaleOrder = objReader["SaleOrder"] is DBNull ? "" : objReader["SaleOrder"].ToString(),
                    Bill = objReader["Bill"] is DBNull ? "" :objReader["Bill"].ToString(),
                    Bill2 = objReader["Bill2"] is DBNull ? "" : objReader["Bill2"].ToString(),
                    CustomerService= objReader["CustomerService"].ToString(),

                    ExpressNo= objReader["ExpressNo"].ToString(),
                    Tel = objReader["TEL"].ToString(),
                    Statues = objReader["Statues"].ToString() == "0" ? "未同步" : "已同步",
                    ChildList = new List<PJSampleChildOut>()
                };
                list.Add(new PJSampleChildOut()
                {
                    Row = Convert.ToInt32(objReader["Row"]),
                    SerialNum = objReader["SerialNum"].ToString(),
                    CustomerOrder=objReader["CustomerOrder"].ToString(),
                    Maktx=objReader["Maktx"].ToString(),
                    Description=objReader["Description"].ToString(),
                    Num = Convert.ToInt32(objReader["Num"]),
                    Rev = objReader["Rev"].ToString(),
                    Ps = objReader["Ps"].ToString()
                });
            }
            if(objPJSampleOut!=null)
            {
                objPJSampleOut.ChildList = list;
            }
            objReader.Close();
            return objPJSampleOut;
        }
        public List<PJSampleExport1> ExportSampleOut1(int workParentId)
        {
            string sql = "select * from PJSampleOuts";
            sql += " left join PJSampleChildOuts on PJSampleOuts.SerialNum=PJSampleChildOuts.SerialNum";
            sql += " where WorkParentId=@WorkParentId";
            List<PJSampleExport1> list = new List<PJSampleExport1>();
            SqlDataReader objReader = SQLHelper.GetReader(sql, new SqlParameter[] { new SqlParameter("@WorkParentId", workParentId) });
            while (objReader.Read())
            {
                list.Add(new PJSampleExport1()
                    {
                        Row = Convert.ToInt32(objReader["Row"]),
                        CustomerAddress = objReader["CustomerAddress"].ToString(),
                        OutDate = objReader["OutDate"] is DBNull ? "": Convert.ToDateTime(objReader["OutDate"]).ToString("yyyy-MM-dd"),
                        Tel = objReader["Tel"].ToString(),
                        Person = objReader["Person"].ToString(),
                        ExpressNo = objReader["ExpressNo"].ToString(),
                        Maktx = objReader["Maktx"].ToString(),
                        Description = objReader["Description"].ToString(),
                        Num = Convert.ToInt32(objReader["Num"])
                    });
            }
            objReader.Close();
            return list;

        }
        public List<PJSampleExport2> ExportSampleOut2(int workParentId)
        {
            string sql = "select * from PJSampleOuts";
            sql += " left join PJSampleChildOuts on PJSampleOuts.SerialNum=PJSampleChildOuts.SerialNum";
            sql += " where WorkParentId=@WorkParentId";
            List<PJSampleExport2> list = new List<PJSampleExport2>();
            SqlDataReader objReader = SQLHelper.GetReader(sql, new SqlParameter[] { new SqlParameter("@WorkParentId", workParentId) });
            while (objReader.Read())
            {
                list.Add(new PJSampleExport2()
                {
                    Row = Convert.ToInt32(objReader["Row"]),
                    CustomerAddress = objReader["CustomerAddress"].ToString(),
                    OutDate = objReader["OutDate"] is DBNull ? "" : Convert.ToDateTime(objReader["OutDate"]).ToString("yyyy-MM-dd"),
                    Customer = objReader["Customer"].ToString(),
                    Person = objReader["Person"].ToString(),
                    Bill = objReader["Bill"].ToString(),
                    SaleOrder = objReader["SaleOrder"].ToString(),
                    Maktx = objReader["Maktx"].ToString(),
                    Num = Convert.ToInt32(objReader["Num"]),
                    CustOrder = objReader["CustomerOrder"].ToString(),
                    Bill2 = objReader["Bill2"].ToString()
                });
            }
            objReader.Close();
            return list;

        }


        public int IsNoSampleOut(int workParentId)
        {
            string sql = "select count(*) from PJSampleOuts where WorkParentId=@WorkParentId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@WorkParentId",workParentId)
            };
            return Convert.ToInt32(SQLHelper.GetSingleResult(sql, param));
        }

        public int GetChildListNum(string serialNum)
        {
            string sql = "select count(*) from PJSampleChildOuts where SerialNum=@SerialNum";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@SerialNum",serialNum)
            };
            return Convert.ToInt32(SQLHelper.GetSingleResult(sql, param));
        }
        #endregion
    }
}
